package com.guojian.jdbc.sql;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.util.Assert;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.guojian.common.map.TableMap;
import com.guojian.common.utils.StringUtilsx;
import com.guojian.jdbc.Constants;
import com.guojian.jdbc.annotation.SqlExp;

/**
 * GuoJian 
 * 
 * Sql构造器,自动根据map,entity生成带条件的sql
 */
public class SqlBuilder {

	public static void buildParamsByMap(Map<String, String> paramsMap, List<Object> sqlParams, StringBuilder sqlBuilder, String paramName, String sql) {
		String paramValue = paramsMap.get(paramName);
		if (StringUtils.isNotBlank(paramValue) && !"null".equalsIgnoreCase(paramValue)) {
			sqlBuilder.append(sql);
			sqlParams.add(paramValue);
		}
	}

	public static void buildSelectWhereSqlByMap(StringBuilder sqlBuilder, TableMap<String, SqlExpression, Object> tableMap, List<Object> sqlParams) {

		/*for (String rowKey : tableMap.keySet()) {
			List<Map<SqlExpression, Object>> ListRowMap = tableMap.get(rowKey);
			for (Map<SqlExpression, Object> rowMap : ListRowMap) {
				for (SqlExpression columnKey : rowMap.keySet()) {
					Object value = rowMap.get(columnKey);
					if (ObjectUtils.isNotEmpty(value) && !"null".equalsIgnoreCase(value.toString())) {
						sqlBuilder.append(" and " + rowKey + columnKey.getValue() + " ? ");
						if (columnKey.getValue().equalsIgnoreCase(SqlExpression.LIKE.getValue())) {
							value = "%" + value + "%";
						}
						sqlParams.add(value);
					}
				}
			}
		}*/

		for (String rowKey : tableMap.keySet()) {
			Map<SqlExpression, Object> columnRowMap = tableMap.get(rowKey);
			for (SqlExpression columnKey : columnRowMap.keySet()) {
				Object value = columnRowMap.get(columnKey);
				if (ObjectUtils.isNotEmpty(value) && !"null".equalsIgnoreCase(value.toString())) {

					if (!sqlBuilder.toString().toLowerCase().contains("where")) {
						sqlBuilder.append(" where 1=1 ");
					}

					sqlBuilder.append(" and " + rowKey + columnKey.getValue() + " ? ");
					if (columnKey.getValue().equalsIgnoreCase(SqlExpression.LIKE.getValue())) {
						value = "%" + StringUtilsx.toString(value) + "%";
					}
					sqlParams.add(value);
				}
			}
		}
	}

	/**
	 * 专用于查询参数的构建,适用于多表,单表
	 * @param sqlBuilder
	 * @param object
	 * @param sqlParams
	 */
	public static <T> void buildSelectWhereSqlByEntity(StringBuilder sqlBuilder, T object, List<Object> sqlParams) {

		EntityInfo<T> entity = new EntityInfo<>(object);
		for (Field field : entity.getFields()) {
			SqlExp fieldSqlExp = entity.getFieldSqlExp(field);
			if (fieldSqlExp == null) {
				continue;
			}
			Object fieldValue = entity.getColumnValue(field);
			appendSelectWhereString(sqlBuilder, sqlParams, entity.getColumnNameWithAlias(field), fieldSqlExp.exp().getValue(), fieldValue);
		}
	}

	public static void appendSelectWhereString(StringBuilder sqlBuilder, List<Object> sqlParams, String columnName, String sqlExp, Object fieldValue) {
		if (fieldValue != null) {
			if (!sqlBuilder.toString().toLowerCase().contains("where")) {
				sqlBuilder.append(" where " + Constants.Sql.TRUE_EXP_SQL);
			}
			sqlBuilder.append("and " + columnName + " " + sqlExp + " ? ");
			sqlParams.add(fieldValue);
		}
	}

	@SuppressWarnings("unused")
	private static <T> void test2(T object) {
		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(object);
		PropertyDescriptor[] propertyDescriptors = bw.getPropertyDescriptors();
		System.out.println("object.getClass() = " + object.getClass());

		for (PropertyDescriptor pd : propertyDescriptors) {
			if (pd.getReadMethod() != null) {
				System.out.println(pd.getName() + "== " + bw.getPropertyValue(pd.getName()));
			}
		}
	}

	public static String deleteAnd(String sql) {
		Assert.notNull(sql, "Sql must be not null");
		return StringUtilsx.delete(sql, Constants.Sql.TRUE_EXP_SQL_AND);
		//return sql.replaceAll(Constants.Sql.TRUE_EXP_SQL_AND, );
	}

	public static void main(String[] args) {
		/*TestBean testBean = new TestBean();
		//testBean.setId(111);
		testBean.setAge(22);
		testBean.setName("guojian");
		testBean.setBeginDate("2020-10-01");
		testBean.setEndDate("2020-11-01");*/

		List<Object> sqlParams = Lists.newArrayList();

		/*StringBuilder stringBuilder = new StringBuilder(" select * from test  where abc > 1 ");
		buildSelectWhereSqlByEntity(stringBuilder, testBean, sqlParams);
		System.out.println("stringBuilder = " + stringBuilder);
		System.out.println("params = " + JSON.toJSONString(sqlParams));*/

		//System.out.println(new EntityInfo<>(testBean).buildInsertSql(sqlParams));

		for (Object object : sqlParams) {
			System.out.println(object);
		}

		/*String newS = StringUtilss.collectionToDelimitedString(sqlParams, ",", "set ", " = ?");
		System.out.println(newS);*/

	}

	@SuppressWarnings("unused")
	private static void test() {
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from a where 1=1 ");

		System.out.println("sb.indexOf(\"wherew\") = " + sb.indexOf("wherew"));

		Map<String, List<Map<SqlExpression, Object>>> mapParams = Maps.newLinkedHashMap();
		Map<SqlExpression, Object> map1 = Maps.newHashMap();
		map1.put(SqlExpression.LIKE, null);

		mapParams.put("name", Arrays.asList(map1));
		mapParams.put("date", Arrays.asList(ImmutableMap.of(SqlExpression.EQUAL_OR_GREATER, "2020-11-11"), ImmutableMap.of(SqlExpression.EQUAL_OR_LESSER, "2020-11-12")));
		mapParams.put("age", Arrays.asList(ImmutableMap.of(SqlExpression.IS_NOT, 5576)));

		List<Object> sqlParams = Lists.newArrayList();

		// SysOperLog log = new SysOperLog();
		// log.setOperName("1111");
		// log.setOperId(1L);
		// log.setDeptName("abc");

		// System.out.println("sb = " + sb);

		TableMap<String, SqlExpression, Object> tableMap = new TableMap<>();
		tableMap.put("a.date", SqlExpression.EQUAL_OR_GREATER, "2020-11-10");
		tableMap.put("a.date", SqlExpression.EQUAL_OR_LESSER, "2020-11-14");
		tableMap.put("b.name", SqlExpression.LIKE, "guojian");
		tableMap.put("c.name", SqlExpression.LIKE, "");
		buildSelectWhereSqlByMap(sb, tableMap, sqlParams);
		System.out.println("sql = " + sb);
		System.out.println("sqlparams = " + JSON.toJSONString(sqlParams));
	}

}
